package com.dao;

import com.model.Student;
import com.model.Teacher;
import com.model.User;
import com.model.Course;
import com.utils.JdbcUtils;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class UserDao {
    
    public User login(String userType, String username, String password) throws SQLException {
        String sql;
        switch (userType) {
            case "student":
                sql = "SELECT Sno as id, Sname as name FROM Student WHERE Sno = ? AND password = ?";
                break;
            case "teacher":
                sql = "SELECT Tno as id, Tname as name FROM Teacher WHERE Tno = ? AND password = ?";
                System.out.println("执行教师登录查询 - Tno: " + username);
                break;
            case "admin":
                sql = "SELECT id, username as name FROM Admin WHERE username = ? AND password = ?";
                break;
            default:
                System.out.println("无效的用户类型: " + userType);
                return null;
        }
        
        System.out.println("=== 执行SQL查询 ===");
        System.out.println("SQL: " + sql);
        System.out.println("参数1(username): " + username);
        System.out.println("参数2(password): " + password);
        
        try (Connection conn = JdbcUtils.getDs().getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            if ("student".equals(userType) || "teacher".equals(userType)) {
                try {
                    pstmt.setInt(1, Integer.parseInt(username));
                } catch (NumberFormatException e) {
                    System.out.println("用户名转换为数字失败: " + username);
                    return null;
                }
            } else {
                pstmt.setString(1, username);
            }
            pstmt.setString(2, password);
            
            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    User user = new User();
                    user.setId(rs.getInt("id"));
                    user.setUsername(username);
                    user.setPassword(password);
                    user.setType(userType);
                    user.setName(rs.getString("name"));
                    System.out.println("查询成功，用户名: " + user.getName());
                    return user;
                } else {
                    System.out.println("未找到匹配的用户记录");
                }
            }
        } catch (SQLException e) {
            System.out.println("=== SQL执行错误 ===");
            e.printStackTrace();
            throw e;
        }
        return null;
    }

    public List<Student> getAllStudents() throws SQLException {
        String sql = "SELECT * FROM Student ORDER BY sno";
        List<Student> students = new ArrayList<>();
        
        try (Connection conn = JdbcUtils.getDs().getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql);
             ResultSet rs = pstmt.executeQuery()) {
            
            while (rs.next()) {
                Student student = new Student();
                student.setSno(rs.getInt("sno"));
                student.setSname(rs.getString("sname"));
                student.setSsex(rs.getInt("ssex"));
                student.setSage(rs.getInt("sage"));
                student.setSdept(rs.getString("sdept"));
                students.add(student);
            }
        }
        return students;
    }

    public List<Teacher> getAllTeachers() throws SQLException {
        String sql = "SELECT * FROM Teacher ORDER BY tno";
        List<Teacher> teachers = new ArrayList<>();
        
        try (Connection conn = JdbcUtils.getDs().getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql);
             ResultSet rs = pstmt.executeQuery()) {
            
            while (rs.next()) {
                Teacher teacher = new Teacher();
                teacher.setTno(rs.getInt("tno"));
                teacher.setTname(rs.getString("tname"));
                teacher.setTsex(rs.getInt("tsex"));
                teacher.setTmobile(rs.getString("tmobile"));
                teachers.add(teacher);
            }
        }
        return teachers;
    }

    public Student getStudentById(int sno) throws SQLException {
        String sql = "SELECT * FROM Student WHERE sno = ?";
        
        try (Connection conn = JdbcUtils.getDs().getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setInt(1, sno);
            
            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    Student student = new Student();
                    student.setSno(rs.getInt("sno"));
                    student.setSname(rs.getString("sname"));
                    student.setSsex(rs.getInt("ssex"));
                    student.setSage(rs.getInt("sage"));
                    student.setSdept(rs.getString("sdept"));
                    return student;
                }
            }
        }
        return null;
    }

    public Teacher getTeacherById(int tno) throws SQLException {
        String sql = "SELECT * FROM Teacher WHERE tno = ?";
        
        try (Connection conn = JdbcUtils.getDs().getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setInt(1, tno);
            
            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    Teacher teacher = new Teacher();
                    teacher.setTno(rs.getInt("tno"));
                    teacher.setTname(rs.getString("tname"));
                    teacher.setTsex(rs.getInt("tsex"));
                    teacher.setTmobile(rs.getString("tmobile"));
                    teacher.setTcompany(rs.getString("tcompany"));
                    return teacher;
                }
            }
        }
        return null;
    }

    public void addStudent(Student student, String password) throws SQLException {
        String sql = "INSERT INTO Student (sno, sname, ssex, sage, sdept, password) VALUES (?, ?, ?, ?, ?, ?)";
        
        try (Connection conn = JdbcUtils.getDs().getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setInt(1, student.getSno());
            pstmt.setString(2, student.getSname());
            pstmt.setInt(3, student.getSsex());
            pstmt.setInt(4, student.getSage());
            pstmt.setString(5, student.getSdept());
            pstmt.setString(6, password);
            
            pstmt.executeUpdate();
        }
    }

    public void updateStudent(Student student) throws SQLException {
        String sql = "UPDATE Student SET sname=?, ssex=?, sage=?, sdept=? WHERE sno=?";
        
        try (Connection conn = JdbcUtils.getDs().getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setString(1, student.getSname());
            pstmt.setInt(2, student.getSsex());
            pstmt.setInt(3, student.getSage());
            pstmt.setString(4, student.getSdept());
            pstmt.setInt(5, student.getSno());
            
            pstmt.executeUpdate();
        }
    }

    public void deleteStudent(int sno) throws SQLException {
        String sql = "DELETE FROM Student WHERE sno=?";
        
        try (Connection conn = JdbcUtils.getDs().getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setInt(1, sno);
            pstmt.executeUpdate();
        }
    }

    public void addTeacher(Teacher teacher, String password) throws SQLException {
        String sql = "INSERT INTO Teacher (tno, tname, tsex, tmobile, password) VALUES (?, ?, ?, ?, ?)";
        
        try (Connection conn = JdbcUtils.getDs().getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setInt(1, teacher.getTno());
            pstmt.setString(2, teacher.getTname());
            pstmt.setInt(3, teacher.getTsex());
            pstmt.setString(4, teacher.getTmobile());
            pstmt.setString(5, password);
            
            pstmt.executeUpdate();
        }
    }

    public void updateTeacher(Teacher teacher) throws SQLException {
        String sql = "UPDATE Teacher SET tname=?, tsex=?, tmobile=? WHERE tno=?";
        
        try (Connection conn = JdbcUtils.getDs().getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setString(1, teacher.getTname());
            pstmt.setInt(2, teacher.getTsex());
            pstmt.setString(3, teacher.getTmobile());
            pstmt.setInt(4, teacher.getTno());
            
            pstmt.executeUpdate();
        }
    }

    public void deleteTeacher(int tno) throws SQLException {
        String sql = "DELETE FROM Teacher WHERE tno=?";
        
        try (Connection conn = JdbcUtils.getDs().getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setInt(1, tno);
            pstmt.executeUpdate();
        }
    }

    public boolean hasStudentAssignments(int sno) throws SQLException {
        String sql = "SELECT COUNT(*) FROM Assignment_Submission WHERE sno = ?";
        
        try (Connection conn = JdbcUtils.getDs().getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setInt(1, sno);
            
            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    return rs.getInt(1) > 0;
                }
            }
        }
        return false;
    }

    public boolean hasTeacherAssignments(int tno) throws SQLException {
        String sql = "SELECT COUNT(*) FROM Assignment WHERE tno = ?";
        
        try (Connection conn = JdbcUtils.getDs().getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setInt(1, tno);
            
            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    return rs.getInt(1) > 0;
                }
            }
        }
        return false;
    }

    public List<Course> getAllCourses() throws SQLException {
        String sql = "SELECT c.*, t.tname FROM Course c LEFT JOIN Teacher t ON c.tno = t.tno ORDER BY c.courseno";
        List<Course> courses = new ArrayList<>();
        
        try (Connection conn = JdbcUtils.getDs().getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql);
             ResultSet rs = pstmt.executeQuery()) {
            
            while (rs.next()) {
                Course course = new Course();
                course.setCno(rs.getString("courseno"));
                course.setCname(rs.getString("coursename"));
                course.setCredit(rs.getInt("coursecredit"));
                course.setTno(rs.getInt("tno"));
                course.setTname(rs.getString("tname"));
                courses.add(course);
            }
        }
        return courses;
    }

    public Course getCourseById(String courseno) throws SQLException {
        String sql = "SELECT c.*, t.tname FROM Course c LEFT JOIN Teacher t ON c.tno = t.tno WHERE c.courseno = ?";
        
        try (Connection conn = JdbcUtils.getDs().getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setString(1, courseno);
            
            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    Course course = new Course();
                    course.setCno(rs.getString("courseno"));
                    course.setCname(rs.getString("coursename"));
                    course.setCredit(rs.getInt("coursecredit"));
                    course.setTno(rs.getInt("tno"));
                    course.setTname(rs.getString("tname"));
                    return course;
                }
            }
        }
        return null;
    }

    public void addCourse(Course course) throws SQLException {
        String sql = "INSERT INTO Course (courseno, coursename, coursecredit, tno) VALUES (?, ?, ?, ?)";
        
        try (Connection conn = JdbcUtils.getDs().getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setString(1, course.getCno());
            pstmt.setString(2, course.getCname());
            pstmt.setInt(3, course.getCredit());
            pstmt.setInt(4, course.getTno());
            
            pstmt.executeUpdate();
        }
    }

    public void updateCourse(Course course) throws SQLException {
        String sql = "UPDATE Course SET coursename=?, coursecredit=?, tno=? WHERE courseno=?";
        
        try (Connection conn = JdbcUtils.getDs().getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setString(1, course.getCname());
            pstmt.setInt(2, course.getCredit());
            pstmt.setInt(3, course.getTno());
            pstmt.setString(4, course.getCno());
            
            pstmt.executeUpdate();
        }
    }

    public void deleteCourse(String courseno) throws SQLException {
        String sql = "DELETE FROM Course WHERE courseno=?";
        
        try (Connection conn = JdbcUtils.getDs().getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setString(1, courseno);
            pstmt.executeUpdate();
        }
    }

    public boolean hasCourseAssignments(String courseno) throws SQLException {
        String sql = "SELECT COUNT(*) FROM Assignment WHERE courseno = ?";
        
        try (Connection conn = JdbcUtils.getDs().getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setString(1, courseno);
            
            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    return rs.getInt(1) > 0;
                }
            }
        }
        return false;
    }
} 